package com.sifeng.test;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by yang_gsh on 2018/12/6.
 * 开发工作量表中标出牵头部门
 */
public class MarkingTheLeadDepartment {
	private static int writeColumnIndex = 2;  //需写入开发工作文档的列数
	private static int readColumnIndex = 0;  //需要写入的文件中需求编号的列数
	private static String departmentName = "营销服务研发部";
	private static String demandListPath = "E:\\hisun\\开发三部\\功能点评估表\\201901\\需求列表_201901.xlsx";    //需要读取的需求列表
	private static String writePath = "E:\\hisun\\开发三部\\功能点评估表\\201901\\需求开发管理开发报表.xlsx"; //需要写入的文件

	public static List<String> getRequirementNumber(String path) throws IOException {
		InputStream iStream=new FileInputStream(path);
		XSSFWorkbook wb = new XSSFWorkbook(iStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		List<String> list = new ArrayList<String>();
		if (sheet == null) {
			return null;
		}

		for(int i = 1; i < sheet.getLastRowNum(); i++) {
			XSSFRow row = sheet.getRow(i);
			XSSFCell cell_A = row.getCell(0);
			if(cell_A == null) {
				continue;
			}
			cell_A.setCellType(Cell.CELL_TYPE_STRING);
			String requirementNumber = cell_A.getStringCellValue();

			if("".equals(requirementNumber.trim())) {
				continue;
			}
			list.add(requirementNumber.trim());
		}

		XSSFSheet sheet3 = wb.getSheetAt(2);
		if (sheet3 == null) {
			return list;
		}

		for(int i = 1; i < sheet3.getLastRowNum(); i++) {
			XSSFRow row = sheet3.getRow(i);
			XSSFCell cell_L = row.getCell(9);
			if(cell_L == null) {
				continue;
			}
			cell_L.setCellType(Cell.CELL_TYPE_NUMERIC);
			double tmp = cell_L.getNumericCellValue();

			if(tmp == 0) {
				continue;
			}

			XSSFCell cell_A = row.getCell(0);
			cell_A.setCellType(Cell.CELL_TYPE_STRING);
			String requirementNumber = cell_A.getStringCellValue();

			if("".equals(requirementNumber.trim())) {
				continue;
			}
			list.add(requirementNumber.trim());
		}

		return list;
	}

	public static void writeExcel(String path, List<String> list) throws IOException {
		InputStream iStream=new FileInputStream(path);
		XSSFWorkbook wb = new XSSFWorkbook(iStream);
		XSSFSheet sheet = wb.getSheetAt(0);
		if (sheet == null) {
			return;
		}

		for(int i = 1; i < sheet.getLastRowNum(); i++) {
			XSSFRow row = sheet.getRow(i);
			XSSFCell cell_E = row.getCell(readColumnIndex);
			if(cell_E == null) {
				continue;
			}
			//cell_E.setCellType(Cell.CELL_TYPE_STRING);
			String requirementNumber = cell_E.getStringCellValue();
			if(list.contains(requirementNumber.trim())) {
				XSSFCell cell_B = row.getCell(writeColumnIndex);
				cell_B.setCellValue(departmentName);
			}
		}

		FileOutputStream outputStream = new FileOutputStream(path);

		wb.write(outputStream);
		outputStream.flush();
		wb.close();
	}

	public static void main(String[] args) throws IOException {
		List<String> list = MarkingTheLeadDepartment.getRequirementNumber(demandListPath);

		System.out.println(list.size());
		for (String s : list) {
			System.out.println(s);
		}
		MarkingTheLeadDepartment.writeExcel(writePath, list);
	}
}
